package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.sun.org.apache.bcel.internal.generic.IFNULL;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Apply;
import cn.gson.crm.model.entity.Customer;
import cn.gson.crm.model.entity.Employee;
import cn.gson.crm.vo.ApplyVo;
import cn.gson.crm.vo.CustomerVo;

public class ApplyDao {
	/**
	 * 根据登录人获取同部门下的所有员工
	 * @param empId
	 * @return
	 * @throws SQLException 
	 */
	public List<Employee> getAllEmployeeByEmpId(int empId) throws SQLException{
		String sql = "select emp_id,emp_name from employee where dept_id = (select dept_id from employee where emp_id = ?) and employee.emp_id != ?";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql, empId,empId);
		List<Employee> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			Employee emp = new Employee();
			emp.setEmpId(Integer.parseInt(mm.get("emp_id").toString()));
			emp.setEmpName(mm.get("emp_name").toString());
			
			list.add(emp);
		}
		return list;
	}
	
	/**
	 * 新增援助
	 * @param apply
	 * @return
	 * @throws SQLException
	 */
	public boolean InsertApply(Apply apply) throws SQLException{
		String sql = "insert apply(emp_id,cust_id,accept_id,emp_name,cust_name) values(?,?,?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql, apply.getEmp_id(),apply.getCust_id(),apply.getAccept_id(),apply.getEmp_name(),apply.getCust_name());
	}
	
	/**
	 * 根据登录人ID查询是否有需要援助的信息
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public List<Apply> getAllNeedApplyByEmpId(int empId) throws SQLException{
		String sql = "select * from apply where accept_id = ? and apply_status_id = 1";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql, empId);
		List<Apply> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			Apply apply = new Apply();
			apply.setAccept_id(Integer.parseInt(mm.get("accept_id").toString()));
			apply.setApply_id(Integer.parseInt(mm.get("apply_id").toString()));
			apply.setCust_id(Integer.parseInt(mm.get("cust_id").toString()));
			apply.setEmp_id(Integer.parseInt(mm.get("emp_id").toString()));
			apply.setEmp_name(mm.get("emp_name").toString());
			apply.setCust_name(mm.get("cust_name").toString());
			
			list.add(apply);
		}
		return list;
	}
	
	/**
	 * 根据ID修改援助状态
	 * @param applyId
	 * @param statusId
	 * @return
	 * @throws SQLException
	 */
	public boolean updateApplyStatusByApplyIdAndStatusId(int applyId,int statusId) throws SQLException{
		String sql = "update apply set apply_status_id = ? where apply_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, statusId, applyId);
	}
	
	/**
	 * 根据登录的人查询是否有需要援助的学员
	 * @param empId
	 * @return
	 * @throws SQLException 
	 */
	public List<CustomerVo> getAllCustomer(int empId) throws SQLException{
		String sql = "select c.cust_id,c.cust_name,cust_gender,cust_address,cust_telphone,cust_degree_id,degree_name,cust_qq,cust_parentTel,cust_parentname,cust_hobby,cust_source,cust_counselor_id,e.emp_name,cust_createtime,cust_drividetime,cust_status_id,status_name from customer c " +
						"inner join employee e on c.cust_counselor_id = e.emp_id " +
						"inner join degree d on d.degree_id = c.cust_degree_id " +
						"inner join `status` s on s.status_id = c.cust_status_id " +
						"inner join apply a on c.cust_id = a.cust_id " +
						"where a.accept_id = ? and a.apply_status_id = 2";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql, empId);
		List<CustomerVo> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			CustomerVo cv = new CustomerVo();
			cv.setCust_id(Integer.parseInt(mm.get("cust_id").toString()));
			cv.setCust_degree_id(Integer.parseInt(mm.get("cust_degree_id").toString()));
			cv.setCust_counselor_id(Integer.parseInt(mm.get("cust_counselor_id").toString()));
			cv.setCust_status_id(Integer.parseInt(mm.get("cust_status_id").toString()));
			cv.setCust_name((String)mm.get("cust_name"));
			cv.setCust_gender((String)mm.get("cust_gender"));
			cv.setCust_address((String)mm.get("cust_address"));
			cv.setCust_telphone((String)mm.get("cust_telphone"));
			cv.setDegree_name((String)mm.get("degree_name"));
			cv.setCust_qq((String)mm.get("cust_qq"));
			cv.setCust_parentTel((String)mm.get("cust_parentTel"));
			cv.setCust_parentName((String)mm.get("cust_parentname"));
			cv.setCust_hobby((String)mm.get("cust_hobby"));
			cv.setCust_source((String)mm.get("cust_source"));
			cv.setEmp_name((String)mm.get("emp_name"));
			cv.setCust_createtime((Timestamp)mm.get("cust_createtime"));
			cv.setCust_drividetime((Timestamp)mm.get("cust_drividetime"));
			cv.setStatus_name((String)mm.get("status_name"));
			
			list.add(cv);
		}
		return list;
	}
	
	/**
	 * 根据登录人ID查询所有请求援助信息
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public List<ApplyVo> getAllApplyInfo(int empId) throws SQLException{
		String sql = "select a.*,e.emp_name a_name from apply a inner join employee e on a.accept_id = e.emp_id where a.emp_id = ?";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql, empId);
		List<ApplyVo> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			ApplyVo vo = new ApplyVo();
			vo.setApply_id(Integer.parseInt(mm.get("apply_id").toString()));
			vo.setApply_status_id(Integer.parseInt(mm.get("apply_status_id").toString()));
			vo.setAccept_id(Integer.parseInt(mm.get("accept_id").toString()));
			vo.setCust_id(Integer.parseInt(mm.get("cust_id").toString()));
			vo.setCust_name(mm.get("cust_name").toString());
			vo.setEmp_id(Integer.parseInt(mm.get("emp_id").toString()));
			vo.setEmp_name(mm.get("emp_name").toString());
			vo.setA_name((String)mm.get("a_name"));
			
			list.add(vo);
		}
		return list;
	}
	
	/**
	 * 根据ID判断是删除还是完成援助
	 * @param applyId
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean updateApplyByApplyIdAndStatusId(int applyId,int id) throws SQLException{
		String sql = null;
		if(id == 3){
			sql = "delete from apply where apply_id = ?";
			return Jdbc.getInstace().executeUpdate(sql, applyId);
		}else{
			sql = "update apply set apply_status_id = 4 where apply_id = ?";
			return Jdbc.getInstace().executeUpdate(sql, applyId);
		}
	}
	
	/**
	 * 双方完成援助后自动修改该学员的状态为已签约
	 * @param custId
	 * @return
	 * @throws SQLException
	 */
	public boolean updateCustomerStatusByCustId(int custId) throws SQLException{
		String sql = "update customer set cust_status_id=2 where cust_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, custId);
	}
	
	/**
	 * 查询是否向同一个人发送援助请求
	 * @param custId
	 * @param empId
	 * @param acceptId
	 * @return
	 * @throws SQLException 
	 */
	public Apply getApplyByCustIdAndEmpIdAndAcceptId(int custId, int empId, int acceptId) throws SQLException{
		String sql = "select * from apply where cust_id = ? and accept_id = ? and emp_id = ?";
		Map<String,Object> map = Jdbc.getInstace().executeQuery(sql, custId,acceptId,empId);
		Apply a = null;
		if(map != null){
			a = new Apply();
			a.setAccept_id(Integer.parseInt(map.get("accept_id").toString()));
			a.setApply_id(Integer.parseInt(map.get("apply_id").toString()));
			a.setEmp_id(Integer.parseInt(map.get("emp_id").toString()));
			a.setCust_id(Integer.parseInt(map.get("cust_id").toString()));
			a.setApply_status_id(Integer.parseInt(map.get("apply_status_id").toString()));
			a.setCust_name(map.get("cust_name").toString());
			a.setEmp_name(map.get("emp_name").toString());
			}
		return a;
	}
}
